package com.googlecode.project.servlets;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class for Servlet: DataViewServlet
 * 
 */
public class DataViewServlet extends javax.servlet.http.HttpServlet implements
		javax.servlet.Servlet {
	static final long serialVersionUID = 1L;
	
	protected ResultSet rs;
	

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#HttpServlet()
	 */
	public DataViewServlet() {
		super();
	}

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#doGet(HttpServletRequest request,
	 *      HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();

		out.println("<html>");
		out.println("<head>");
		out.println("</head>");
		out.println("<body>");
		out.println("<form action=\"");
		out.println(request.getRequestURI());
		out.println("\" method=\"post\">");
		out.println("<input type=\"submit\" ");
		out.println("value=\"View Requests \">  ");
		out.println("</form>");
		out.println("</body>");
		out.println("</html>");
		out.close();
	}

	/*
	 * (non-Java-doc)
	 * 
	 * @see javax.servlet.http.HttpServlet#doPost(HttpServletRequest request,
	 *      HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		response.setContentType("text/html");
		PrintWriter out = response.getWriter();

		out.println("<html>");
		out.println("<head>");
		out.println("<title>The Mobile Teaching Assistant's Assistant</title>");
		out.println("<link rel=stylesheet href='style.css' type='text/css'>");
		out.println("</head>");
		out.println("<body id='twocolumn-left'>");
		out.println("<div id='container'>");
		out.println("<div class='wrapper'>");
		out.println("<div id='header'>");
		out.println("<div class='wrapper'>");
		out
				.println("<h1 id='page-title'><div id='g_title'><div style='text-align: center;'>The Mobile Teaching Assistant's Assistant<br></div></div></h1>");
		out.println("<div style='clear: both'></div>");
		out
				.println("<p class='description'/><div id='g_description'><p style='text-align: center;'/>|    <a href='index.html'>Home</a>    |    <a href='http://www.ucd.ie'>ucd.ie</a>    |    <a href='/project/DataViewServlet'>View Requests</a>    |	<a href='statistics.jsp'>View Statistics</a>	| <br></div>");
		out.println("<div style='clear: both'></div>");
		out.println("</div>");
		out.println("</div>");

		// debugging info

		long time1 = System.currentTimeMillis();

		// connecting to database

		Connection con = null;
		Statement stmt = null;
		rs = null;

		try {
			Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
			con = DriverManager.getConnection("jdbc:derby:/DerbyDB/AssetDB");
			stmt = con.createStatement();
			rs = stmt.executeQuery(selectQuery());
			
//			out.println("<form action=\"");
//			out.println("/project/remove.jsp");
//			out.println("\" method=\"post\">");
//			out.println("<input type=\"submit\" ");
//			out.println("value=\"Remove\">  ");
			
			while (rs.next()) {
				int current = 0;
				out.println("<div id='main-content'>");
				out.println("<div class='wrapper'>");
				out.println("<div class='content-item'><div id='g_body'><p style='text-align: justify; font-family: verdana;'><font size='2'></font></p></div></div>");
				String x = rs.getObject(6).toString();
				String supervisor = rs.getObject(8).toString();
				int solved = Integer.parseInt(x);
				if (solved != 0) {
					out.println("<h2><b><font color='red'>Solved by: " + supervisor + "</font></b></h2>");
				} else {
					out.println("<a href='/project/remove.jsp'><font color='green'><h2><b>Request # "
							+ rs.getObject(1) + "</b></h2></font></a>\n");
				}
				
				out.println("<h4><b>Query ID</b></h4>");
				out.println(rs.getObject(1));
				out.println("<h4><b>Title</b></h4>");
				out.println(rs.getObject(2));
				out.println("<h4><b>Description</b></h4>");
				out.println(rs.getObject(3) + "\n");
				out.println("<h4><b>Received From</b></h4>");
				out.println(rs.getObject(4) + "\n");
				out.println("<h4><b>Time Received</b></h4>");
				out.println(rs.getObject(5) + "\n");
				out.println("<h4><b>Query Priority</b></h4>");
				out.println(rs.getObject(7) + " %\n");
				out.println("<div style='clear: both'></div>");
				out.println("</div>");
				out.println("</div>");
			}
		} catch (SQLException e) {
			throw new ServletException("Servlet Could not display records.", e);
		} catch (ClassNotFoundException e) {
			throw new ServletException("JDBC Driver not found.", e);
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (SQLException e) {
			}
		}

		out.println("<div id='sidebar'>");
		out.println("<div class='wrapper'>");
		out.println("<div class='links'>");
		out
				.println("<div class='wrapper'><div id='g_sidebar'><p style='text-align: justify;'> <span style='font-family: verdana;'></span><br></p></div></div>");
		out.println("<div style='clear: both'></div>");
		out.println("</div>");
		out.println("</div>");
		out.println("</div>");
		out.println("<div id='footer'><div class='wrapper'>");
		out.println("<hr />");
		out
				.println("<p><div id='g_footer'><p style='text-align: center;'/>UCD School of Computer Science & Informatics <br></div>");
		out.println("<div style='clear: both'></div>");
		out.println("</div></div>");
		out.println("<!-- /wrapper --><!-- /footer -->");
		out.println("</div>");
		out.println("</div>");
		out
				.println("<div id='extraDiv1'><span></span></div><div id='extraDiv2'><span></span></div>");
		out
				.println("<div id='extraDiv3'><span></span></div><div id='extraDiv4'><span></span></div>");
		out
				.println("<div id='extraDiv5'><span></span></div><div id='extraDiv6'><span></span></div>");
		out.println("</body>");
		out.println("</html>");
		out.close();
	}
	
	/**
	 * @return
	 */
	public String selectQuery() {
		return "SELECT query, title, description, ip_address, request_time, viewed, query_priority, checked_by FROM REQUEST_DETAILS ORDER BY viewed, query_priority DESC";
	}
}